﻿<%@ WebHandler Language="C#" Class="CommonActionHandle" %>
using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using DatabaseAccess.DBUtility;
/*
 * Description:通用查询方法
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * create Date:2022.02.16
 */
public class CommonActionHandle : IHttpHandler
{
    class Result
    {
        public object data { get; set; }
        public Meta meta { get; set; }
        public int total { get; set; }
        public class Meta
        {
            public int status;
            public string msg;
            public Meta(int status, string msg)
            {
                this.status = status;
                this.msg = msg;
            }
        }
    }

    public class TreeObject
    {
        //全局变量用于保存数据
        private List<TreeObject> treeNode = new List<TreeObject>();

        public string label { get; set; }
        public string value { get; set; }

        public IList<TreeObject> children = new List<TreeObject>();

        /// <summary>
        /// 递归生产树
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="parentNode">父节点</param>
        /// <param name="parentID">节点的归属id</param>
        public List<TreeObject> BindTree(DataTable dtSource, TreeObject parentNode, string parentID)
        {
            DataRow[] rows = dtSource.Select(string.Format("parentid = '{0}'", parentID));
            foreach (DataRow row in rows)
            {
                TreeObject tree = new TreeObject();
                tree.label = row["label"].ToString();
                tree.value = row["id"].ToString();

                //递归
                BindTree(dtSource, tree, row["id"].ToString());

                // 如果没有子节点，将children设为null
                if (tree.children.Count == 0)
                {
                    tree.children = null;
                }

                if (parentNode == null)
                {
                    treeNode.Add(tree);
                }
                else
                {
                    parentNode.children.Add(tree);
                }
            }
            return treeNode;
        }

    }
    
    TreeObject t = new TreeObject();
    List<TreeObject> treeNodes = new List<TreeObject>();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getProductFamily":
                        r = getProductFamily();//查询机型
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenter":
                        r = getWorkcenter();//查询工区
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenterInfo":
                        r = getWorkcenterInfo(jObject);//查询工区
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryUsersByRoleName":
                        r = getFactoryUsersByRoleName(jObject);//根据角色查询分厂人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryUsers":
                        r = getFactoryUsers(jObject);//查询分厂下所有员工
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEmployeInfo":
                        r = getEmployeeInfo(jObject);//查询员工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getRoleList":
                        r = getRoleList(jObject);//查询MES角色列表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTeam":
                        r = getTeam(jObject);//查询班组Team
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactory":
                        r = getFactory(jObject);//查询分厂
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainernameAsync":
                        r = getContainernameAsync(jObject);//模糊匹配获取批次号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getProductnameAsync":
                        r = getProductnameAsync(jObject);//模糊匹配获取件号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMfgordernameAsync":
                        r = getMfgordernameAsync(jObject);//模糊匹配获取订单
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEmployeeAsync":
                        r = getEmployeeAsync(jObject);//模糊匹配获取MES人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getBpmUserAsync":
                        r = getBpmUserAsync(jObject);//模糊匹配BPM人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEngineNoAsync":
                        r = getEngineNoAsync(jObject);//模糊匹配发动机号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerNoAsync":
                        r = getContainerNoAsync(jObject);//模糊匹配任务单号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getBPMUserListByDeptID":
                        r = getBPMUserListByDeptID(jObject);//根据组织id查询BPM人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getResourcenameAsync":
                        r = getResourcenameAsync(jObject);//查询设备编号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryListByCode":
                        r = getFactoryListByCode(jObject);//根据分厂代码查分厂
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCustomerList":
                        r = getCustomerList(jObject);//查询外委厂家
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getOrdertypeList":
                        r = getOrdertypeList(jObject);//查询订单类别
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSgdNoAsync":
                        r = getSgdNoAsync(jObject);//查询施工单号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getReportPageAsync":
                        r = getReportPageAsync(jObject);//查询报表信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDealFaultBillNameAsync":
                        r = getDealFaultBillNameAsync(jObject);//查询排故单号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "isSuperAdmin":
                        r = isSuperAdmin(jObject);//查询当前用户是否信息中心管理员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUserWhiteList":
                        r = getUserWhiteList(jObject);//获取用户权限白名单
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMESOrginazition":
                        r = getMESOrginazition(jObject);//查询MES系统组织结构
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryInfo":
                        r = getFactoryInfo(jObject);//查询分厂信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTeamNameAsync":
                        r = getTeamNameAsync(jObject);//模糊匹配班组名称
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTopGZProductnameAsync":
                        r = getTopGZProductnameAsync(jObject);//模糊匹配工装顶层件号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUserInfo":
                        r = getUserInfo(jObject);//查询用户信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkerList":
                        r = getWorkerList(jObject);//查询工人列表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getGZTopContainername":
                        r = getGZTopContainername(jObject);//模糊匹配顶层批次号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getGZTopProductname":
                        r = getGZTopProductname(jObject);//模糊匹配顶层图号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMESZSOrginazition":
                        r = getMESZSOrginazition(jObject);//MES装配厂组织结构(分厂/工区/班/组)4级结构
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMenuDefinitionNameAsync":
                        r = getMenuDefinitionNameAsync(jObject);//模糊匹配用户菜单
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPurchasenoAsync":
                        r = getPurchasenoAsync(jObject);//模糊匹配采购订单号
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getVendorList":
                        r = getVendorList(jObject);//查询供应商
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPageDriverState":
                        r = getPageDriverState(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "isInAllowedIp":
                        r = isInAllowedIp(context);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询员工信息
    Result getEmployeeInfo(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT e.employeename,e.employeeid,e.fullname,
       f.factoryname,f.factoryid,
       wc.workcentername,wc.workcenterid,
       s.shiftname,nvl(e.shiftid,te.shiftid) shiftid,
       te.teamname,te.teamid,
       eroles.rolenames
FROM employee e 
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @"
LEFT JOIN mes_factory_mapping f ON f.factoryid = sv.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = sv.factoryid";
        }
        sql += @"
LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid 
LEFT JOIN operation o ON o.operationid = sv.operationid
LEFT JOIN 
( 
  SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames
  FROM employeerole er 
  LEFT JOIN employee e ON e.employeeid = er.employeeid  
  LEFT JOIN roledef r ON r.roleid = er.roleid  
  GROUP BY e.employeeid 
) eroles ON eroles.employeeid = e.employeeid 
LEFT JOIN 
(
  SELECT  t.teamname,t.teamid,te.employeeid,t.shiftid 
  FROM teamemployee te 
  LEFT JOIN team t ON t.teamid = te.teamid 
) te  ON te.employeeid = e.employeeid AND ROWNUM = 1
LEFT JOIN shift s ON s.shiftid = nvl(e.shiftid,te.shiftid)
WHERE e.employeeid = '{0}'
";
        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查机型
    Result getProductFamily()
    {
        Result r = new Result();
        DataTable dt = DatabaseHelper.Query_ReturnDatTable("SELECT pf.productfamilyid,pf.productfamilyname FROM productfamily pf");
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询分厂及工区信息
    Result getWorkcenter()
    {
        Result r = new Result();
        string sql = @"
SELECT f.factoryid,f.factoryname,w.workcenterid,w.workcentername
FROM factory f 
LEFT JOIN Workcenter w ON w.factoryid = f.factoryid
WHERE w.factoryid IS NOT NULL
ORDER BY f.factoryid";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //根据角色查询分厂人员
    Result getFactoryUsersByRoleName(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT DISTINCT e.employeename,e.employeeid,e.fullname
FROM roledef rd
LEFT JOIN employeerole er ON er.roleid = rd.roleid
LEFT JOIN employee e ON e.employeeid = er.employeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = sv.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE e.fullname IS NOT NULL ";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        if (jObject["rolename"] != null && jObject["rolename"].ToString() != "")
        {
            string rolename = jObject["rolename"].ToString();
            if (rolename.IndexOf('|') != -1)
            {
                string[] list = rolename.Split('|');
                string roles = "(";
                foreach (var i in list)
                {
                    roles += "'" + i + "',";
                }
                roles = roles.TrimEnd(',');
                roles += ")";
                sql += string.Format(" AND rd.rolename IN {0}", roles);
            }
            else
            {
                sql += string.Format(" AND rd.rolename = '{0}'", jObject["rolename"].ToString());
            }
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询分厂员工
    Result getFactoryUsers(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT e.employeename,e.employeeid,e.fullname
FROM employee e
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = sv.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE 1=1 ";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询MES角色列表
    Result getRoleList(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT rd.rolename
FROM roledef rd
WHERE rd.rolename NOT LIKE '%Default%' AND rd.rolename NOT IN ('SPC','Portal Configuration','Mfg Audit Trail Inquiry','Security Administration','Login')";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询
    Result getTeam(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.teamid,t.teamname
FROM team t
LEFT JOIN Workcenter w ON w.workcenterid = t.workcenterid";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = w.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = w.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE t.teamid IS NOT NULL ";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND w.workcentername = '{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //获取分厂列表
    Result getFactory(JObject jObject)
    {
        string sql = "";
        //是否查询分厂映射
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql = @"
SELECT t.factoryid,t.factoryname,t.factorycode 
FROM mes_factory_mapping t
WHERE t.factorycode IS NOT NULL";
            if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND t.factoryid = '{0}'", jObject["factoryid"].ToString());
            }
            if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND t.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            sql += " ORDER BY t.factoryname";
        }
        else
        {
            sql = @"
SELECT f.factoryid,f.factoryname,ua.attributevalue factorycode
FROM factory f
LEFT JOIN Userattribute ua ON f.factoryid = ua.parentid AND ua.userattributename = '分厂代码'
WHERE ua.attributevalue IS NOT NULL";
            if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
            }
            if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
            sql += " ORDER BY f.factoryname";
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询工区信息
    Result getWorkcenterInfo(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT w.workcenterid,w.workcentername,w.wctype
FROM Workcenter w";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" 
LEFT JOIN mes_factory_mapping f ON f.factoryid = w.factoryid";
        }
        else
        {
            sql += @"
LEFT JOIN factory f ON f.factoryid = w.factoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
        }
        sql += @"
WHERE w.notes IS NULL ";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND w.workcentername = '{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }

        if (jObject["workcentertype"] != null)
        {
            sql += " AND w.wctype IS NULL ";
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配获取批次号
    Result getContainernameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @" SELECT rownum,cci.containerid,cci.containername FROM containercurrentinfo cci";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(cci.containername) LIKE '%{0}%'", jObject["containername"].ToString().ToUpper());
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配获取批次号
    Result getProductnameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @" SELECT rownum,pb.productname FROM productbase pb";
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(pb.productname) LIKE '%{0}%'", jObject["productname"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配获取订单号
    Result getMfgordernameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @" SELECT rownum,mfg.mfgordername FROM mfgorder mfg";
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(mfg.mfgordername) LIKE '%{0}%'", jObject["mfgordername"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配MES人员
    Result getEmployeeAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @" SELECT rownum,e.fullname,e.employeeid,e.employeename FROM employee e WHERE 1=1 ";
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(e.fullname) LIKE '%{0}%'", jObject["fullname"].ToString().ToUpper());
        }
        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(e.employeename) LIKE '%{0}%'", jObject["employeename"].ToString().ToUpper());
        }
        if (jObject["employeeid"] != null && jObject["employeeid"].ToString() != "")
        {
            sql += string.Format(" AND e.employeeid LIKE '%{0}%'", jObject["employeeid"].ToString());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配BPM人员
    Result getBpmUserAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @" SELECT rownum,e.username,e.userid,e.userid || '<' || e.username || '>' fullname FROM bpm_orguser_table e WHERE 1=1 ";
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND e.username LIKE '%{0}%'", jObject["fullname"].ToString().ToUpper());
        }
        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND e.userid LIKE '%{0}%'", jObject["employeename"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配发动机号
    Result getEngineNoAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.engineno,rownum
FROM 
(
  SELECT DISTINCT u.attributevalue engineno
  FROM Userattribute u
  WHERE u.userattributename = '发动机'
";
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(u.attributevalue) LIKE '%{0}%'", jObject["engineno"].ToString().ToUpper());
        }

        sql += ") t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配任务单号
    Result getContainerNoAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.containerno,rownum
FROM 
(
  SELECT DISTINCT ep.containerno
  FROM enginepartplan ep
";
        if (jObject["containerno"] != null && jObject["containerno"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(ep.containerno) LIKE '%{0}%'", jObject["containerno"].ToString().ToUpper());
        }

        sql += ") t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //根据部门id查询bpm系统人员
    Result getBPMUserListByDeptID(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.userid,t.username
FROM bpm_orguser_table t
WHERE t.departmentid IN (
  SELECT bp.departmentid
  FROM bpm_orgdepartment_table bp
  START WITH ";
        if (jObject["departmentid"] != null && jObject["departmentid"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["departmentid"].ToString());
            if (arr.Count != 0)
            {
                sql += string.Format(" bp.departmentid = '{0}'", arr[arr.Count - 1].ToString());
            }
        }
        sql += @"
  CONNECT BY PRIOR bp.departmentid = bp.parentdepartmentid
) ";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配设备编号
    Result getResourcenameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.resourcename,rownum
FROM 
(
  SELECT rd.resourcename
  FROM Resourcedef rd 
";
        if (jObject["resourcename"] != null && jObject["resourcename"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(rd.resourcename) LIKE '%{0}%'", jObject["resourcename"].ToString().ToUpper());
        }

        sql += ") t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //根据分厂代码查询分厂列表
    Result getFactoryListByCode(JObject jObject)
    {
        /*
        无损检测中心:  305
        盘轴制造分厂:  600
        机匣分厂:  601
        热表分厂:  602
        发动机总体事业部:  603
        工装工具分公司: 604
        燃烧部件事业部: 607
        压气机叶片事业部:  608
        结构件制造分厂: 609
        检验检测中心:  613
         */
        Result r = new Result();
        string sql = "";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql = @"
SELECT f.factoryid,f.factoryname,f.factorycode attributevalue
FROM mes_factory_mapping f
WHERE f.factorycode IS NOT NULL ";
            if (jObject["factoryCode"] != null && jObject["factoryCode"].ToString() != "")
            {
                string factoryCode = jObject["factoryCode"].ToString();
                if (factoryCode.IndexOf('|') != -1)
                {
                    string[] list = factoryCode.Split('|');
                    string roles = "(";
                    foreach (var i in list)
                    {
                        roles += "'" + i + "',";
                    }
                    roles = roles.TrimEnd(',');
                    roles += ")";
                    sql += string.Format(" AND f.factorycode IN {0}", roles);
                }
                else
                {
                    sql += string.Format(" AND f.factorycode = '{0}'", jObject["factoryCode"].ToString());
                }
            }

            sql += " ORDER BY f.factorycode ";
        }
        else
        {
            sql = @"
SELECT f.factoryid,f.factoryname,u.attributevalue
FROM factory f
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE u.attributevalue IS NOT NULL ";
            if (jObject["factoryCode"] != null && jObject["factoryCode"].ToString() != "")
            {
                string factoryCode = jObject["factoryCode"].ToString();
                if (factoryCode.IndexOf('|') != -1)
                {
                    string[] list = factoryCode.Split('|');
                    string roles = "(";
                    foreach (var i in list)
                    {
                        roles += "'" + i + "',";
                    }
                    roles = roles.TrimEnd(',');
                    roles += ")";
                    sql += string.Format(" AND u.attributevalue IN {0}", roles);
                }
                else
                {
                    sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factoryCode"].ToString());
                }
            }

            sql += " ORDER BY u.attributevalue ";
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询外委厂家
    Result getCustomerList(JObject jObject)
    {
        Result r = new Result();
        string sql = @"SELECT cst.customerid,cst.customername FROM customer cst";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询订单类别
    Result getOrdertypeList(JObject jObject)
    {
        Result r = new Result();
        string sql = @"SELECT ot.ordertypeid,ot.ordertypename FROM ordertype ot ORDER BY ot.ordertypename";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询施工单号
    Result getSgdNoAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.workflowname,rownum
FROM 
(
  SELECT wfb.workflowname
  FROM Workflowbase wfb 
";
        if (jObject["workflowname"] != null && jObject["workflowname"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(wfb.workflowname) LIKE '%{0}%'", jObject["workflowname"].ToString().ToUpper());
        }
        sql += @"
) t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询报表名称
    Result getReportPageAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"SELECT t.*,rownum FROM (SELECT t.pagename,t.pageurl,t.pageid FROM mes_reportpage t WHERE 1=1";
        if (jObject["pagename"] != null && jObject["pagename"].ToString() != "")
        {
            sql += string.Format(" AND t.pagename LIKE '%{0}%'", jObject["pagename"].ToString());
        }
        if (jObject["pageurl"] != null && jObject["pageurl"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(t.pageurl) LIKE '%{0}%'", jObject["pageurl"].ToString().ToUpper());
        }
        sql += " ) t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询排故单号
    Result getDealFaultBillNameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.dealfaultbillname,rownum
FROM 
(
  SELECT db.dealfaultbillname
  FROM dealfaultbill db 
";
        if (jObject["dealfaultbillname"] != null && jObject["dealfaultbillname"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(db.dealfaultbillname) LIKE '%{0}%'", jObject["dealfaultbillname"].ToString().ToUpper());
        }
        sql += @"
) t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询当前用户是否信息中心管理员
    Result isSuperAdmin(JObject jObject)
    {
        string sql = @"SELECT e.employeename,e.fullname FROM employee e WHERE e.employeename IN ('69400364','59100621', '50102249','59602132','59800618','59701402') AND e.employeeid = '{0}'";//寇延婷、梅东、彭恒鑫
        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dtWhiteList = DatabaseHelper.Query_ReturnDatTable(sql);
        Result r = new Result();
        r.data = dtWhiteList;
        r.total = Convert.ToInt32(dtWhiteList.Rows.Count);
        r.meta = new Result.Meta(200, "查询成功");
        return r;
    }

    //获取用户权限白名单
    Result getUserWhiteList(JObject jObject)
    {
        string sql = @"
SELECT au.employeeid
FROM mes_report_auth_users au
LEFT JOIN 
(
    SELECT tt.pageid,CASE WHEN INSTR(tt.pageurl,'?',1) = 0 THEN tt.pageurl ELSE SUBSTR(tt.pageurl,1,INSTR(tt.pageurl,'?',1)-1) END pageurl
    FROM mes_reportpage tt
) r ON r.pageid = au.reportpageid
WHERE lower(r.pageurl) = '{0}'";
        sql = string.Format(sql, jObject["pageurl"].ToString().ToLower());
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "获取成功");
        return r;
    }

    //查询MES组织结构(分厂/工区/班组)
    Result getMESOrginazition(JObject jObject)
    {
        Result r = new Result();
        string factoryCode = "";
        string factorycodeList = "";
        if (jObject["factoryCode"] != null && jObject["factoryCode"].ToString() != "")
        {
            factoryCode = jObject["factoryCode"].ToString();
            if (factoryCode.IndexOf('|') != -1)
            {
                string[] list = factoryCode.Split('|');
                factorycodeList = "(";
                foreach (var i in list)
                {
                    factorycodeList += "'" + i + "',";
                }
                factorycodeList = factorycodeList.TrimEnd(',');
                factorycodeList += ")";
            }
        }

        string sql = @"
SELECT te.id,te.departmentname label,te.orgtype,
       CASE WHEN te.parentid IS NOT NULL THEN te.parentid ELSE '0' END parentid
FROM (
  SELECT t.teamid ID,t.teamname departmentname,t.workcenterid parentid, '班组' orgtype
  FROM team t
  LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
  WHERE t.workcenterid IS NOT NULL AND w.workcentername IS NOT NULL  
  UNION ALL
  SELECT w.workcenterid ID,w.workcentername departmentname,w.factoryid parentid, '工区' orgtype
  FROM workcenter w
  LEFT JOIN factory f ON f.factoryid = w.factoryid
  WHERE w.factoryid IS NOT NULL AND f.factoryname IS NOT NULL";

        //是否包含处室
        if (jObject["wctype"] != null && jObject["wctype"].ToString() == "是")
        {
            sql += " AND 1=1";
        }
        else
        {
            sql += " AND w.notes IS NULL AND w.wctype IS NULL";
        }

        sql += @"
  UNION ALL
  SELECT f.factoryid ID,f.factoryname departmentname,NULL parentid,'分厂' orgtype
";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" FROM mes_factory_mapping f ";
            if (factoryCode != "")
            {
                if (factoryCode.IndexOf('|') > -1)
                {
                    sql += string.Format(" WHERE f.factorycode IN {0}", factorycodeList);
                }
                else
                {
                    sql += string.Format(" WHERE f.factorycode = '{0}'", factoryCode);
                }

            }
            else
            {
                sql += " WHERE f.factorycode <> '603'";
            }
        }
        else
        {
            sql += @"
  FROM factory f 
  LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
            if (factoryCode != "")
            {
                if (factoryCode.IndexOf('|') > -1)
                {
                    sql += string.Format(" WHERE u.attributevalue IN {0}", factorycodeList);
                }
                else
                {
                    sql += string.Format(" WHERE u.attributevalue = '{0}'", factoryCode);
                }

            }
            else
            {
                sql += " WHERE u.attributevalue <> '603'";
            }
        }

        sql += @"
) te
";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        treeNodes = t.BindTree(dt, null, "0");
        r.data = treeNodes;
        r.total = Convert.ToInt32(0);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询分厂信息
    Result getFactoryInfo(JObject jObject)
    {
        string sql = "";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql = "SELECT f.factoryid,f.factoryname,f.factorycode FROM mes_factory_mapping f WHERE 1=1 ";
        }
        else
        {
            sql = @"
SELECT f.factoryid,f.factoryname,u.attributevalue factorycode
FROM factory f
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE 1=1 ";
        }

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["factorycode"] != null && jObject["factorycode"].ToString() != "")
        {
            if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
            {
                sql += string.Format(" AND f.factorycode = '{0}'", jObject["factorycode"].ToString());
            }
            else
            {
                sql += string.Format(" AND u.attributevalue = '{0}'", jObject["factorycode"].ToString());
            }
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //模糊匹配班组信息
    Result getTeamNameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.teamname,rownum
FROM 
(
    SELECT te.teamname,te.teamid
    FROM team te 
    LEFT JOIN workcenter w ON w.workcenterid = te.workcenterid
    LEFT JOIN factory f ON f.factoryid = w.factoryid
    WHERE 1=1 ";
        if (jObject["teamname"] != null && jObject["teamname"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(te.teamname) LIKE '%{0}%'", jObject["teamname"].ToString().ToUpper());
        }
        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND t.teamid = '{0}'", jObject["teamid"].ToString());
        }
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(w.workcentername) LIKE '%{0}%'", jObject["workcentername"].ToString().ToUpper());
        }
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(f.factoryname) LIKE '%{0}%'", jObject["factoryname"].ToString().ToUpper());
        }
        sql += @"
) t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配顶层工装件号
    Result getTopGZProductnameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT rownum,pb.productname
FROM product p
LEFT JOIN productbase pb ON p.productbaseid = pb.productbaseid
WHERE p.istopgz = 1 ";
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(pb.productname) LIKE '%{0}%'", jObject["productname"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询用户信息
    Result getUserInfo(JObject jObject)
    {
        string sql = @" 
SELECT e.employeeid,e.employeename,e.fullname,f.factoryname,f.factoryid,
       w.workcentername,w.workcenterid,t.teamname,t.teamid,er.rolenames,u.attributevalue factorycode
FROM employee e
LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN teamemployee te ON te.employeeid = e.employeeid
LEFT JOIN team t ON t.teamid = te.teamid
LEFT JOIN 
( 
  SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames  
  FROM employeerole er 
  LEFT JOIN employee e ON e.employeeid = er.employeeid  
  LEFT JOIN roledef r ON r.roleid = er.roleid  
  GROUP BY e.employeeid 
) er ON er.employeeid = e.employeeid
LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE e.employeeid = '{0}'";

        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dtResult = DatabaseHelper.Query_ReturnDatTable(sql);
        dtResult.Columns.Add("auth", typeof(string));

        foreach (DataRow dr in dtResult.Rows)
        {
            string rolenames = dr["rolenames"].ToString();
            if (rolenames.Contains("定额员") || rolenames.Contains("系统管理员") || rolenames.Contains("分厂领导") || rolenames.Contains("分厂管理者"))
            {
                dr["auth"] = "管理员";
                break;
            }

            if (dr["factoryname"].ToString() == "")
            {
                dr["auth"] = "管理员";
                break;
            }

            if (rolenames.Contains("工区长"))
            {
                if (dr["workcentername"].ToString() == "")
                {
                    dr["auth"] = "管理员";
                    break;
                }
                else
                {
                    dr["auth"] = "工区长";
                    break;
                }
            }

            if (rolenames.Contains("班组长"))
            {
                if (dr["teamname"].ToString() == "")
                {
                    if (dr["workcentername"].ToString() == "")
                    {
                        dr["auth"] = "管理员";
                        break;
                    }
                    else
                    {
                        dr["auth"] = "工区长";
                        break;
                    }
                }
                else
                {
                    dr["auth"] = "班组长";
                    break;
                }
            }

            if (rolenames.Contains("工人"))
            {
                dr["auth"] = "工人";
                break;
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询工人列表
    Result getWorkerList(JObject jObject)
    {
        string sql = @" 
SELECT distinct e.employeeid,e.employeename,e.fullname
FROM employee e
LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN teamemployee te ON te.employeeid = e.employeeid
LEFT JOIN team t ON t.teamid = te.teamid
WHERE 1=1 ";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND sv.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND sv.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //班组
        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND te.teamid = '{0}'", jObject["teamid"].ToString());
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //模糊匹配顶层批次号
    Result getGZTopContainername(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT rownum,cci.containername
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
WHERE p.istopgz = 1";
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(cci.containername) LIKE '%{0}%'", jObject["containername"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配顶层图号
    Result getGZTopProductname(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT rownum,t.productname
FROM (
    SELECT DISTINCT pb.productname
    FROM product p
    LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
    WHERE p.istopgz = 1";
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND UPPER(pb.productname) LIKE '%{0}%'", jObject["productname"].ToString().ToUpper());
        }
        sql += @" ) t";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //查询MES装配厂组织结构(分厂/工区/班/组)
    Result getMESZSOrginazition(JObject jObject)
    {
        Result r = new Result();
        string factoryCode = "";
        string factorycodeList = "";
        if (jObject["factoryCode"] != null && jObject["factoryCode"].ToString() != "")
        {
            factoryCode = jObject["factoryCode"].ToString();
            if (factoryCode.IndexOf('|') != -1)
            {
                string[] list = factoryCode.Split('|');
                factorycodeList = "(";
                foreach (var i in list)
                {
                    factorycodeList += "'" + i + "',";
                }
                factorycodeList = factorycodeList.TrimEnd(',');
                factorycodeList += ")";
            }
        }

        string sql = @"
SELECT te.id,te.departmentname label,te.orgtype,
       CASE WHEN te.parentid IS NOT NULL THEN te.parentid ELSE '0' END parentid
FROM (
  SELECT t.teamid ID,t.teamname departmentname,t.shiftid parentid, '组' orgtype
  FROM team t
  LEFT JOIN shift s ON s.shiftid = t.shiftid
  WHERE t.workcenterid IS NOT NULL AND s.shiftname IS NOT NULL
  UNION ALL
  SELECT s.shiftid ID,s.shiftname departmentname,w.workcenterid parentid, '班' orgtype
  FROM shift s
  LEFT JOIN workcenter w ON s.workcenterid = w.workcenterid
  WHERE s.shiftid IS NOT NULL AND w.workcentername IS NOT NULL
  UNION ALL
  SELECT w.workcenterid ID,w.workcentername departmentname,w.factoryid parentid, '工区' orgtype
  FROM workcenter w
  LEFT JOIN factory f ON f.factoryid = w.factoryid
  WHERE w.factoryid IS NOT NULL AND f.factoryname IS NOT NULL AND w.notes IS NULL
  UNION ALL
  SELECT f.factoryid ID,f.factoryname departmentname,NULL parentid,'分厂' orgtype";
        if (jObject["isMapping"] != null && jObject["isMapping"].ToString() == "是")
        {
            sql += @" FROM mes_factory_mapping f ";
            if (factoryCode != "")
            {
                if (factoryCode.IndexOf('|') > -1)
                {
                    sql += string.Format(" WHERE f.factorycode IN {0}", factorycodeList);
                }
                else
                {
                    sql += string.Format(" WHERE f.factorycode = '{0}'", factoryCode);
                }

            }
        }
        else
        {
            sql += @"
  FROM factory f 
  LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'";
            if (factoryCode != "")
            {
                if (factoryCode.IndexOf('|') > -1)
                {
                    sql += string.Format(" WHERE u.attributevalue IN {0}", factorycodeList);
                }
                else
                {
                    sql += string.Format(" WHERE u.attributevalue = '{0}'", factoryCode);
                }

            }
        }

        sql += @" ) te";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        treeNodes = t.BindTree(dt, null, "0");
        r.data = treeNodes;
        r.total = Convert.ToInt32(0);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //模糊匹配用户菜单
    Result getMenuDefinitionNameAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"SELECT rownum,md.menudefinitionid,md.menudefinitionname FROM menudefinition md";
        if (jObject["menudefinitionname"] != null && jObject["menudefinitionname"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(md.menudefinitionname) LIKE '%{0}%'", jObject["menudefinitionname"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    //模糊匹配采购订单号
    Result getPurchasenoAsync(JObject jObject)
    {
        Result r = new Result();
        string sql = @"SELECT rownum,erp.purchaseno FROM erppurchasemfgorder erp";
        if (jObject["purchaseno"] != null && jObject["purchaseno"].ToString() != "")
        {
            sql += string.Format(" WHERE UPPER(erp.purchaseno) LIKE '%{0}%'", jObject["purchaseno"].ToString().ToUpper());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }
    
    //查询供应商
    Result getVendorList(JObject jObject)
    {
        string sql = @"SELECT vd.vendorid,vd.vendorname,vd.description,vd.notes FROM vendor vd WHERE 1=1";
        if (jObject["vendorname"] != null && jObject["vendorname"].ToString() != "")
        {
            string vendorname = jObject["vendorname"].ToString();
            if (vendorname.IndexOf('|') != -1)
            {
                string[] list = vendorname.Split('|');
                string roles = "(";
                foreach (var i in list)
                {
                    roles += "'" + i + "',";
                }
                roles = roles.TrimEnd(',');
                roles += ")";
                sql += string.Format(" AND vd.vendorname IN {0}", roles);
            }
            else
            {
                sql += string.Format(" AND vd.vendorname = '{0}'", jObject["vendorname"].ToString());
            }
        }

        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }

    Result getPageDriverState(JObject jObject)
    {
        string sql = @"SELECT t.*
FROM mes_reportpage_log t
LEFT JOIN mes_reportpage rp ON t.pageid = rp.pageid
LEFT JOIN employee e ON e.employeeid = t.employeeid
WHERE rp.pageurl LIKE '%{0}%' AND e.employeeid = '{1}'";
        sql = string.Format(jObject["pageurl"].ToString(), jObject["employeeid"].ToString());
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "ok");
        return r;
    }
    
    Result isInAllowedIp(HttpContext context)
    {
        string[] IPAddressList = { "::1", "localhost", "10.15.7.17", "20.12.131.51", "10.15.7.75", "10.15.7.76", "20.12.43.70", "20.12.43.41", "20.12.105.55","20.12.117.126" };
        string ipAddress = context.Request.UserHostAddress.ToString();
        List<string> list = new List<string>(IPAddressList);
        Result r = new Result();
        r.data = list.Contains(ipAddress);
        r.total = 0;
        r.meta = new Result.Meta(200, "ok");
        return r;
    }
}